<?php
namespace cosocket\system;

class SqlBuilder
{
    private $table = '';
    private $fields = '*';
    private $where = [];
    private $orderBy = [];
    private $limit = '';
    private $groupBy = '';
    private $having = [];
    private $joins = [];

    public function table(string $table, ?string $alias = null): self
    {
        $table = $this->escapeIdentifier($table);
        if ($alias !== null) {
            $table .= ' AS ' . $this->escapeIdentifier($alias);
        }
        $this->table = $table;
        return $this;
    }

    public function fields($fields = '*'): self
    {
        if (is_array($fields)) {
            $fields = array_map([$this, 'escapeIdentifier'], $fields);
            $this->fields = implode(',', $fields);
        } else {
            $this->fields = $fields;
        }
        return $this;
    }

    public function where($field, $value = null, $operator = '='): self
    {
        if (is_array($field)) {
            foreach ($field as $key => $value) {
                $this->where($key, $value);
            }
            return $this;
        }

        $field = $this->escapeIdentifier($field);
        if (is_array($value)) {
            $values = array_map([$this, 'escapeValue'], $value);
            $this->where[] = "{$field} IN (" . implode(',', $values) . ")";
        } else {
            $value = $this->escapeValue($value);
            $this->where[] = "{$field} {$operator} {$value}";
        }
        return $this;
    }

    public function orWhere($field, $value, $operator = '='): self
    {
        $currentWhere = $this->buildWhereCondition($field, $value, $operator);
        if (!empty($this->where)) {
            $this->where[] = "OR {$currentWhere}";
        } else {
            $this->where[] = $currentWhere;
        }
        return $this;
    }

    public function join(string $table, string $condition, string $type = 'INNER', ?string $alias = null): self
    {
        $table = $this->escapeIdentifier($table);
        if ($alias !== null) {
            $table .= ' AS ' . $this->escapeIdentifier($alias);
        }
        $this->joins[] = "{$type} JOIN {$table} ON {$condition}";
        return $this;
    }

    public function groupBy(string $field): self
    {
        $this->groupBy = "GROUP BY " . $this->escapeIdentifier($field);
        return $this;
    }

    public function having($condition): self
    {
        $this->having[] = $condition;
        return $this;
    }

    public function orderBy($field, string $direction = 'ASC'): self
    {
        $field = $this->escapeIdentifier($field);
        $this->orderBy[] = "{$field} {$direction}";
        return $this;
    }

    private function escapeIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    private function escapeValue($value): string
    {
        if ($value === null) {
            return 'NULL';
        }
        if (is_bool($value)) {
            return $value ? '1' : '0';
        }
        if (is_numeric($value)) {
            return $value;
        }
        return "'" . addslashes($value) . "'";
    }

    private function buildWhereCondition($field, $value, $operator): string
    {
        $field = $this->escapeIdentifier($field);
        if (is_array($value)) {
            $values = array_map([$this, 'escapeValue'], $value);
            return "{$field} IN (" . implode(',', $values) . ")";
        }
        $value = $this->escapeValue($value);
        return "{$field} {$operator} {$value}";
    }

    public function limit(int $one, ?int $two = null): self
    {
        if ($two !== null) {
            $this->limit = "LIMIT {$one}, {$two}";
        } else {
            $this->limit = "LIMIT {$one}";
        }
        return $this;
    }

    public function get(): string
    {
        $sql = ["SELECT {$this->fields} FROM {$this->table}"];

        if (!empty($this->joins)) {
            $sql[] = implode(' ', $this->joins);
        }

        if (!empty($this->where)) {
            $sql[] = 'WHERE ' . implode(' AND ', $this->where);
        }

        if ($this->groupBy) {
            $sql[] = $this->groupBy;
        }

        if (!empty($this->having)) {
            $sql[] = 'HAVING ' . implode(' AND ', $this->having);
        }

        if (!empty($this->orderBy)) {
            $sql[] = 'ORDER BY ' . implode(', ', $this->orderBy);
        }

        if ($this->limit) {
            $sql[] = $this->limit;
        }
        $this->reset();
        return implode(' ', $sql);
    }

    public function insert(array $data): string
    {
        $columns = array_map([$this, 'escapeIdentifier'], array_keys($data));
        $values = array_map([$this, 'escapeValue'], array_values($data));
        $this->reset();
        return sprintf(
            "INSERT INTO %s (%s) VALUES (%s)",
            $this->table,
            implode(', ', $columns),
            implode(', ', $values)
        );
    }

    public function insertBatch(array $dataSet): string
    {
        if (empty($dataSet)) {
            throw new \InvalidArgumentException('数据集不能为空');
        }

        $columns = array_map(
            [$this, 'escapeIdentifier'],
            array_keys(reset($dataSet))
        );

        $values = [];
        foreach ($dataSet as $data) {
            $values[] = '(' . implode(', ', array_map(
                [$this, 'escapeValue'],
                array_values($data)
            )) . ')';
        }
        $this->reset();
        return sprintf(
            "INSERT INTO %s (%s) VALUES %s",
            $this->table,
            implode(', ', $columns),
            implode(', ', $values)
        );
    }

    public function update(array $data): string
    {
        $set = [];
        foreach ($data as $field => $value) {
            $field = $this->escapeIdentifier($field);
            $value = $this->escapeValue($value);
            $set[] = "{$field} = {$value}";
        }

        $sql = ["UPDATE {$this->table} SET " . implode(', ', $set)];

        if (!empty($this->where)) {
            $sql[] = 'WHERE ' . implode(' AND ', $this->where);
        }
        $this->reset();
        return implode(' ', $sql);
    }

    public function delete(): string
    {
        $sql = ["DELETE FROM {$this->table}"];

        if (!empty($this->where)) {
            $sql[] = 'WHERE ' . implode(' AND ', $this->where);
        }

        if (!empty($this->orderBy)) {
            $sql[] = 'ORDER BY ' . implode(', ', $this->orderBy);
        }

        if ($this->limit) {
            $sql[] = $this->limit;
        }
        $this->reset();
        return implode(' ', $sql);
    }

    public function reset(): self
    {
        $this->table = '';
        $this->fields = '*';
        $this->where = [];
        $this->orderBy = [];
        $this->limit = '';
        $this->groupBy = '';
        $this->having = [];
        $this->joins = [];
        return $this;
    }
}